package com.haierp.util;

import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.haierp.model.Good;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ExcelUtil {

	
	public static List<Good> export(){
		List<Good> goodList = new ArrayList<Good>();
		Workbook workbook =null; 
		try {
			workbook = Workbook.getWorkbook(new File("C:\\Users\\admin\\Desktop\\hai\\2017新建SKU-new - 副本.xls"));
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Sheet sheet = workbook.getSheet(0);
		
		
		for(int i=1;i<=100;i++){
			Cell a0 = sheet.getCell(0,i); 
			Cell a1 = sheet.getCell(1,i); 
			Cell a2 = sheet.getCell(2,i); 
			Cell a3 = sheet.getCell(3,i); 
			Cell a4 = sheet.getCell(4,i); 
			Cell a5 = sheet.getCell(5,i); 
			Cell a6 = sheet.getCell(6,i); 
			Cell a7 = sheet.getCell(7,i); 
			Cell a8 = sheet.getCell(8,i); 
			Cell a9 = sheet.getCell(9,i); 
			Cell a10 = sheet.getCell(10,i); 
			Cell a11 = sheet.getCell(11,i); 
			Cell a12 = sheet.getCell(12,i); 
			Cell a13 = sheet.getCell(13,i); 
			Cell a14 = sheet.getCell(14,i); 
			Cell a15 = sheet.getCell(15,i); 
			Cell a16 = sheet.getCell(16,i); 
			Cell a17 = sheet.getCell(17,i); 
			Cell a18 = sheet.getCell(18,i); 
			Cell a19 = sheet.getCell(19,i); 
			Cell a20 = sheet.getCell(20,i); 
			Cell a21 = sheet.getCell(21,i); 
			Cell a22 = sheet.getCell(22,i); 
			String str0 = a0.getContents(); 
			String str1 = a1.getContents(); 
			String str2 = a2.getContents(); 
			String str3 = a3.getContents(); 
			String str4 = a4.getContents(); 
			String str5 = a5.getContents(); 
			String str6 = a6.getContents(); 
			String str7 = a7.getContents(); 
			String str8 = a8.getContents(); 
			String str9 = a9.getContents(); 
			String str10 =a10.getContents(); 
			String str11 =a11.getContents(); 
			String str12 =a12.getContents();  
			String str13 =a13.getContents(); 
			String str14 =a14.getContents(); 
			String str15 =a15.getContents(); 
			String str16 =a16.getContents(); 
			String str17 =a17.getContents(); 
			String str18 =a18.getContents(); 
			String str19 =a19.getContents(); 
			String str20= a20.getContents();
			String str21= a21.getContents();
			String str22= a22.getContents();
			Good good  = new Good();
			good.setSkuCode(str0);
			good.setName(str1);
			good.setCategoryName(str2);
			good.setEnName(str3);
			good.setItemShort(str4);
			good.setMainPic(str5);
			if(StringUtils.isNotEmpty(str6))
			{
//				DateUtils
//				DateFormatUtils.format(str6, DateFormatUtils.ISO_DATE_FORMAT.getPattern());
				Date startDate = null;
				try {
					startDate=DateUtil.convertStr2Date(str6, DateUtil.SIMPLE_DATE_PARTEN);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				good.setStartDate(startDate);
			}
			if(StringUtils.isNotEmpty(str7))
			{
				Date endDate = null;
				try {
					endDate=DateUtil.convertStr2Date(str7, DateUtil.SIMPLE_DATE_PARTEN);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				good.setEndDate(endDate);
			}
			
			
			good.setColor(str8);
			good.setScale(str9);
			
			good.setSalePrice(Double.parseDouble(str11));
			good.setAntciFreight(Double.parseDouble(str12));
			good.setSalable(Integer.parseInt(str15));
			good.setSellInventory(Integer.parseInt(str17));
//			good.setSalePrice(salePrice);
			if(StringUtils.isNotEmpty(str14))
			{
				Date createDate = null;
				try {
					createDate=DateUtil.convertStr2Date(str14, DateUtil.DATE_PARTEN_MILLI);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				good.setGmtCreate(createDate);
			}
			
			good.setBarcode(str16);
			good.setBrand(str18);
			good.setSaleType(str19);
			good.setItemCode(str20);
//			good.setGmtCreate(new Date());
			good.setGmtModify(new Date());
			
			
			
			if (!"NULL".equals(str21)) {
				good.setInventory(Integer.parseInt(str21));	
			}
			if (!"NULL".equals(str22)) {
				good.setVirtualInventory(Integer.parseInt(str22));
			}
			goodList.add(good);
		}
		workbook.close();
//		for(int i=1;i<=2356;i++){
//			Cell a1 = sheet.getCell(i,1); 
//		}
		
		return goodList;
	}
}
